Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


By setting goals for how you expect the system to perform, you can easily determine whether you are successful. You can also determine earlier in the design process whether you will be able to achieve those goals.

Review of OLTP Characteristics

By analyzing the characteristics of the OLTP system and the goals you want to achieve, you gather a lot of data with which you can build the system. Much of the design of the system is determined by the data access patterns and specifications of the system.

In an OLTP system, data access is generally random, with perhaps some sequential components. The OLTP system usually handles a lot of concurrent data access from many users simultaneously. By configuring the system and carefully distributing the I/O load, you can achieve a high level of concurrency.

I encourage you to relate the information in the next part of this chapter to your particular configuration. Look for similarities and differences and decide how you can benefit from the tuning guidelines presented in previous chapters. Remember that when we talk about the design of the system, we are talking about every aspect of the system, from the client application code to the hardware.

Many design considerations you use to set up your system are based on knowing your application and system. It is important to spend the time up front to carefully examine how the system should run; only then can you determine the design of the system. The end product depends enormously on the amount of time and effort you put into the original design. No amount of tuning after the system is in production can make up for poor design choices.

Design Considerations

The first part of this chapter provided you with a good idea of how the OLTP system operates based on the data access patterns. Before you look at the design of the system, here is a review of a few of the concepts described in earlier chapters:

  I/Os are usually the limiting factor in your system. You can only do a fixed number of random I/Os per second per disk drive (refer to Chapter 14, “Advanced Disk I/O Concepts”).
  I/Os can be reduced by caching data blocks in the SGA. If data you want to access is already in the SGA, a disk I/O is not required.
  Isolate sequential I/Os. Most of the time spent reading from or writing to the disk is spent seeking to where the data is located. If seeks can be reduced, more I/Os per second can be achieved.
  Spread out random I/Os. Random I/Os have a maximum rate per drive. By spreading the I/Os out among many drives, the overall rate can be increased.
  Avoid paging and swapping. Any time the system pages or swaps, performance is severely degraded. Avoid this at all costs.

These factors contribute to determining the optimal data layout of the system. The physical layout—along with SGA and shared pool tuning—creates an optimally configured server for the typical OLTP application. Of course, proper design of the application is just as important, but that topic is addressed in Part IV of this book, “Tuning SQL.”

Physical Data Layout

This section looks at how the data on the system should be configured on the physical disks. First, it looks at how to lay out the data on traditional disks; then it looks at disk arrays. I recommend using disk arrays if at all possible; the ease of use and performance benefits are worth the cost of the array.

The main goal in designing the physical data layout is to isolate the sequential I/Os and to provide balanced I/Os across all the disks that are randomly accessed. Earlier in this chapter, you learned that the redo log files and the archive log files in an OLTP system are all accessed sequentially. You also know that the majority (if not all) of the data files are accessed in a random fashion.

Many of the concepts presented here represent the best-case scenario. I realize that budgetary constraints do not permit everyone to buy the optimal number of disks for this configuration. Make the most of these guidelines. Remember that it is the number of disks that provide performance in an OLTP system; if you have the opportunity to buy one 4 gigabyte disk drive or two 2 gigabyte disk drives, the best performance comes from the two 2 gigabyte disk drives.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.